CREATE PROCEDURE [dbo].[Report_SpaceBaseForFloorplan]
	@RangeReviewId int
AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @endDatePY DATE = DATEADD(Year, -1, @endDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId)
declare @totalCoverage INT = @totalStores * @totalMonths

SELECT 
  pla.Name PlanogramName
, pl.Name PlanogramLocation
, pla.Level1
, pla.Level2
, sp.Level4
, ar.Code ArticleCode
, ar.UOM
, ar.Name ArticleName
, ar.Width 
, sp.Facings PlanogramFacings
, ar.Facings TotalFacings
, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(sales.Sales / (1 + ar.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
, CASE WHEN am.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
	   WHEN scm.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
	   WHEN cm.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
	   ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM	
, ISNULL(SUM(inv.Cost * ex.Rate) / CAST(@totalCoverage AS MONEY),0) as InventoryCost
, setup.LinearMeters
, setup.SquareMeters
FROM Articles ar 
INNER JOIN Categories cat on ar.CategoryId = cat.Id 
INNER JOIN Spaces sp on ar.Id = sp.ArticleId
INNER JOIN Planograms pla on pla.Id = sp.PlanogramId
INNER JOIN PlanogramLocations pl on pl.Id = pla.PlanogramLocationId
INNER JOIN RangeReviews rr on ar.RangeReviewId = rr.Id
INNER JOIN Countries co on rr.CountryId = co.Id	
INNER JOIN Currencies cur on rr.CountryId = cur.CountryId
LEFT JOIN RangeReviewSetup setup on setup.RangeReviewId = rr.Id AND setup.CategoryId = ar.CategoryId
LEFT JOIN TheoreticalMargins am  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
LEFT JOIN TheoreticalMargins scm on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
LEFT JOIN TheoreticalMargins cm  on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
LEFT JOIN ArticleSales sales on sales.ArticleId = ar.Id
			AND ((sales.Date BETWEEN @startDatePY AND @endDatePY) OR (sales.Date BETWEEN @startDate AND @endDate))
			AND sales.Confirmed = 1 
			AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)
LEFT JOIN Inventories inv on inv.ArticleId = sales.ArticleId
								 and inv.Date = sales.Date
								 and inv.StoreId = sales.StoreId
LEFT OUTER JOIN CurrencyExchangeRates ex on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
where setup.LinearMeters = 1 OR setup.SquareMeters = 1
group by 
	pla.Name
  , pl.Name
  , pla.Level1
  , pla.Level2
  , sp.Level4
  , ar.Code
  , ar.UOM
  , ar.Name  
  , ar.Width
  , sp.Width
  , ar.Facings
  , sp.Facings  
  , co.IsVATIncluded
  , am.Margin
  , cm.Margin
  , scm.Margin
  , setup.LinearMeters
  , setup.SquareMeters

